package com.xdf.docmanagementsystem.common.util;

import com.xdf.docmanagementsystem.dto.ExcelModel;
import com.xdf.docmanagementsystem.exc.NotAllowedException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: doc-management-system
 * @description: 文件读取类, 用于读取各种文件, 并将文件中的数据映射到当前项目
 * @author: Zhang
 * @create: 2020-06-26 21:18
 **/
public class ExcelUtil {

    private static final String FULL_DATE_TIME_FORMATTER = "yyyy/MM/dd HH:mm:ss";
    private static final String DATE_FORMATTER = "yyyy/MM/dd";
    private static final String SET = "set";
    private static final String GET = "get";

    /**
     * 将Excel文件转换为javaBean,Excel第一列为字段列
     *
     * @param inputStream   输入流
     * @param fileName      文件名
     * @param clazz         bean的Class对象
     * @param readAllSheets 是否读取所有Sheet
     * @param startRowIndex 数据的读取行
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> conventExcelFileToBean(InputStream inputStream, String fileName, Class<T> clazz, boolean readAllSheets, int startRowIndex) throws Exception {
        List<T> list = new ArrayList<T>();
        Workbook workbook = getWorkBookFromFile(inputStream, fileName);
        int sheetCount = workbook.getNumberOfSheets();
        if (sheetCount <= 0) return list;
        for (int i = 0; i < sheetCount; i++) {
            if (i > 0 && !readAllSheets) break;
            Sheet sheet = workbook.getSheetAt(i);
            //  获取Excel上对应的列名称
            Map<String, Integer> fields = new LinkedHashMap<>();
            Row firstRow = sheet.getRow(0);
            int countOfRows = sheet.getPhysicalNumberOfRows();
            int countOfCells = firstRow.getPhysicalNumberOfCells();
            for (int num = 0; num < countOfCells; num++) {
                Cell cell = firstRow.getCell(num);
                String field = cell.getRichStringCellValue().getString();
                if (!StringUtils.isEmpty(field)) {
                    fields.put(field.trim(), num);
                }
            }
            for (int numberOfRow = startRowIndex; numberOfRow < countOfRows; numberOfRow++) {
                Row row = sheet.getRow(numberOfRow);
                T object = clazz.newInstance();
                for (Field field : clazz.getDeclaredFields()) {
                    if (Modifier.isStatic(field.getModifiers())) continue;
                    field.setAccessible(true);
                    Method method = clazz.getMethod(findSetterMethod(field), field.getType());
                    String fieldName = field.getName();
                    Class<?> type = field.getType();
                    Integer cellIndex = fields.get(fieldName);
                    if (cellIndex == null) continue;
                    Cell cell = row.getCell(cellIndex);
                    Object val = getCellValue(cell) == null ? "" : String.valueOf(getCellValue(cell));
                    Object o = conventType(type, String.valueOf(val));
                    method.invoke(object, o);
                }
                list.add(object);
            }
        }
        return list;
    }

    /**
     * 创建Excel模板
     *
     * @param outputStream
     * @param rowIndex
     * @param map
     * @return
     * @throws Exception
     */
    public static boolean createExcelTemplate(OutputStream outputStream, int rowIndex, Map<Integer, ExcelModel> map) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        Row row = sheet.createRow(rowIndex);
        Row commentRow = sheet.createRow(++rowIndex);
        for (Integer key : map.keySet()) {
            ExcelModel value = map.get(key);
            Cell cell = row.createCell(key);
            Cell commentCell = commentRow.createCell(key);
            cell.setCellValue(value.getCellValue());
            commentCell.setCellValue(value.getComment());
            sheet.setColumnWidth(key, (int) (value.getComment().getBytes().length * 1.2d * 256 > 12 * 256 ? value.getComment().getBytes().length * 1.2d * 256 : 12 * 256));
        }
        try {
            workbook.write(outputStream);
            return true;
        } catch (IOException e) {
            return false;
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 转换类型
     *
     * @param clazz
     * @param value
     * @return
     */
    public static Object conventType(Class<?> clazz, String value) {
        if (StringUtils.isEmpty(value)) return null;
        if (Integer.class == clazz || int.class == clazz) {
            if (value.contains(".")) value = value.substring(0, value.indexOf('.'));
            return Integer.parseInt(value);
        } else if (Double.class == clazz || double.class == clazz) {
            return Double.parseDouble(value);
        } else if (Long.class == clazz || long.class == clazz) {
            if (value.contains(".")) value = value.substring(0, value.indexOf('.'));
            return Long.parseLong(value);
        } else if (Short.class == clazz || short.class == clazz) {
            return Short.parseShort(value);
        } else if (Boolean.class == clazz || boolean.class == clazz) {
            return Boolean.parseBoolean(value);
        } else if (Byte.class == clazz || byte.class == clazz) {
            return Byte.parseByte(value);
        } else if (Float.class == clazz || float.class == clazz) {
            return Float.parseFloat(value);
        } else if (BigDecimal.class == clazz) {
            return new BigDecimal(value);
        } else {
            return value;
        }
    }

    /**
     * 是否Excel文件
     *
     * @param fileName
     * @return
     */
    public static boolean isExcelFile(String fileName) {
        if (StringUtils.isEmpty(fileName)) return false;
        return fileName.endsWith(".xls") || fileName.endsWith(".xlsx");
    }

    /**
     * 获取Workbook
     *
     * @param inputStream
     * @param fileName
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBookFromFile(InputStream inputStream, String fileName) throws IOException {
        Workbook workbook = null;
        if (!isExcelFile(fileName)) throw new NotAllowedException("该文件不是Excel文件!");
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 获取单元格值
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        if (cell == null) return null;
        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_STRING) {
            return cell.getRichStringCellValue().getString();
        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue();
        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cellType == Cell.CELL_TYPE_ERROR) {
            return null;
        } else if (cellType == Cell.CELL_TYPE_BLANK) {
            return null;
        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
            return "";
        } else {
            return cell.getStringCellValue();
        }
    }

    /**
     * 获取Set方法
     *
     * @param field
     * @return
     */
    public static String findSetterMethod(Field field) {
        return SET + StringUtil.capitalize(field.getName());
    }

    /**
     * 获取Get方法
     *
     * @param field
     * @return
     */
    public static String findGetterMethod(Field field) {
        return GET + StringUtil.capitalize(field.getName());
    }

}
